package LW.com.Db;

import java.sql.*;
import java.util.*;

import LW.com.Model.*;

public class DBOperation extends DBconnetion {

	public List<Book> queryAllbook() {
		List<Book> Allbook = new ArrayList<Book>();

		String sql = "select * from Book";
		try {
			Connection conn = getConnection();
			PreparedStatement preparedstatement = conn.prepareStatement(sql);
			ResultSet result = preparedstatement.executeQuery();
			while (result.next()) {
				Book onebook = new Book();
				onebook.setBook_id(result.getInt(1));
				onebook.setBook_name(result.getString(2));
				Allbook.add(onebook);
			}
			conn.close();
			return Allbook;
		} catch (Exception e) {
			System.out.println("queryAllbook : " + e.toString());
		}

		return null;
	}

	public List<User> queryAllUser() {
		List<User> Alluser = new ArrayList<User>();

		String sql = "select * from UserInfo";
		try {
			Connection conn = getConnection();
			PreparedStatement preparedstatement = conn.prepareStatement(sql);
			ResultSet result = preparedstatement.executeQuery();
			while (result.next()) {
				User oneuser = new User();
				oneuser.setId(result.getInt(1));
				oneuser.setName(result.getString(2));
				oneuser.setPassword(result.getString(3));
				Alluser.add(oneuser);
			}
			conn.close();
			return Alluser;
		} catch (Exception e) {
			System.out.println("queryAlluser : " + e.toString());
		}

		return null;
	}

	public User checkout(String username) {

		User oneuser = new User();
		String sql = "select * from userInfo where name =?";
		try {
			Connection conn = getConnection();
			PreparedStatement preparestatement = conn.prepareStatement(sql);
			preparestatement.setString(1, username);
			ResultSet result = preparestatement.executeQuery();
			while (result.next()) {
				oneuser.setId(result.getInt(1));
				oneuser.setName(result.getString(2));
				oneuser.setPassword(result.getString(3));
			}
			if (oneuser.getId() != 0) {
				conn.close();
				return oneuser;
			}
		} catch (Exception e) {
			System.out.println("checkout has error : " + e.toString());
		}
		return null;
	}
	
	public Book checkout(int book_id) {

		Book oneuser = new Book();
		String sql = "select * from Book where book_id =?";
		try {
			Connection conn = getConnection();
			PreparedStatement preparestatement = conn.prepareStatement(sql);
			preparestatement.setInt(1, book_id);
			ResultSet result = preparestatement.executeQuery();
			while (result.next()) {
				oneuser.setBook_id(result.getInt(1));
				oneuser.setBook_name(result.getString(2));
			}
			if (oneuser.getBook_id() != 0) {
				conn.close();
				return oneuser;
			}
		} catch (Exception e) {
			System.out.println("checkout has error : " + e.toString());
		}
		return null;
	}

	public int Maxrows(String tablename) {
		try {
			Connection conn = getConnection();
			String sql = "select count(book_id) from " + tablename;
			PreparedStatement pre = conn.prepareStatement(sql);
			int rows = 0;
			ResultSet re = pre.executeQuery();
			while (re.next()) {
				rows = re.getInt(1);
			}
			conn.close();
			return rows;
		} catch (Exception e) {
			System.out.println("Maxrows has error , it is " + e.toString());
		}
		return 0;
	}

	public Boolean addNewthing(Object newinfo, String whichtable) {

		Connection connection = getConnection();
		try {
			int influrow = 0;
			if (whichtable.equals("userInfo")) {
				User newuser = (User)newinfo;
				String sql = "insert into "+whichtable+" values(?,?)";
				PreparedStatement preparedStatement = connection.prepareStatement(sql);
				preparedStatement.setString(1, newuser.getName());
				preparedStatement.setString(2, newuser.getPassword());
				influrow = preparedStatement.executeUpdate();
			}else if(whichtable.equals("Book")) {
				Book newbook = (Book)newinfo;
				String sql = "insert into "+whichtable+" values(?)";
				PreparedStatement preparedStatement = connection.prepareStatement(sql);
				preparedStatement.setString(1,newbook.getBook_name());
				influrow = preparedStatement.executeUpdate();
			}
			if (influrow > 0) {
				connection.close();
				return true;
			}
		} catch (Exception e) {
			System.out.println("adduser has error : " + e.toString());
		}
		return false;
	}
	
	
	public Boolean updateInfo(Book one) {
		try {
			Connection connection = getConnection();
			String sql = "update Book set book_name=? where book_id=?";
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setString(1, one.getBook_name());
			pre.setInt(2, one.getBook_id());
			int state = pre.executeUpdate();
			if(state > 0) {
				connection.close();
				return true;
			}
		} catch (Exception e) {
			System.out.println("updateinfo has error : " +e.toString());
		}
		return false;
	}
	
	public Boolean adduser(User newuser) {
		
		try {
			Connection connection = getConnection();
			String sql = "insert into userInfo values(?,?)";
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setString(1, newuser.getName());
			pre.setString(2, newuser.getPassword());
			int changerows = pre.executeUpdate();
			if(changerows>0) {
				connection.close();
				return true;
			}
		}catch (Exception e) {
			System.out.println("addnewuser has error : "+e.toString());
		}
		
		return false;
	}
	
	public Boolean deletebook(int book_id) {
		try {
			Connection conn = getConnection();
			String sql = "delete from Book where book_id="+book_id;
			PreparedStatement pre = conn.prepareStatement(sql);
			int state  = pre.executeUpdate();
			if(state>0) {
				conn.close();
				return true;
			}
		}catch (Exception e) {
			System.out.println("delete has error : " + e.toString());
		}
		
		return false;
	}
}
